package dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

import trip.Company;
import trip.HibernateUtil;
import trip.Person;
import trip.Route;
import trip.Stop;
import trip.Trip;

public class TripDAOImpl extends GenericDaoImpl<Trip> implements TripDAO {
	public TripDAOImpl(Class<Trip> type) {
		super(type);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Trip> getByPerson(Person person) {
		Session session = null;
		List<Trip> result = new ArrayList<Trip>();
		if (person == null)
			return result;
		try {
			session = HibernateUtil.getFactory().openSession();
			session.beginTransaction();
			Query query = session.createQuery("select pt.trip from Person_trip pt "
					+ "where pt.person = :id");
			query.setLong("id", person.getId());
			result = query.list();
			session.getTransaction().commit();
			return result;
		} finally {
			if (session != null && session.isOpen())
				session.close();
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Trip> getByRoute(Route route) {
		Session session = null;
		List<Trip> result = new ArrayList<Trip>();
		if (route == null)
			return result;
		try {
			session = HibernateUtil.getFactory().openSession();
			session.beginTransaction();
			Query query = session.createQuery("select distinct t from Trip t "
					+ "where t.route = :id");
			query.setLong("id", route.getId());
			result = query.list();
			session.getTransaction().commit();
			return result;
		} finally {
			if (session != null && session.isOpen())
				session.close();
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Trip> getByFilters(Date in, Date out, Stop departure,
			Stop arrival, Integer seats, Integer minprice, Integer maxprice, 
			Integer minstops, Integer maxstops, Integer number, List<Company> companies) {
		Session session = null;
		List<Trip> result = new ArrayList<Trip>();
		if (departure == null || 
			arrival == null)
			return result;
		if (seats == null)
			seats = 0;
		if (minprice == null)
			minprice = 0;
		if (maxprice == null)
			maxprice = Integer.MAX_VALUE;
		if (minstops == null)
			minstops = 0;
		if (maxstops == null)
			maxstops = Integer.MAX_VALUE;
		if (seats < 0 || minprice < 0 || maxprice < 0 || maxprice < minprice || 
			minstops < 0 || maxstops < 0 || maxstops < minstops)
			return result;
		try {
			session = HibernateUtil.getFactory().openSession();
			session.beginTransaction();
			Query in_query = null, out_query = null;
			Query stops_query = session.createQuery("select rs1.route "
					+ "from Route_stops rs1, Route_stops rs2  "
					+ "where rs1.route = rs2.route and "
					+ "rs1.stop = :did and rs2.stop = :aid "
					+ "and rs1.stop_number < rs2.stop_number");
			stops_query.setLong("did", departure.getId());
			stops_query.setLong("aid", arrival.getId());
			if (stops_query.list().isEmpty())
				return result;
			if (in != null && out != null && in.after(out))
				return result;
			if (in != null) {
				in_query = session.createQuery("select tt.trip from Time_table tt "
					+ "where tt.departure >= :tin and tt.stop_number = "
					+ "(select rs.stop_number from Route_stops rs "
					+ "where rs.route = tt.trip.route "
					+ "and rs.stop = :sin)").setTimestamp("tin", in).setParameter("sin", departure);
			}
			if (out != null) {
				out_query = session.createQuery("select tt.trip from Time_table tt "
					+ "where tt.arrival <= :tout and tt.stop_number = "
					+ "(select rs.stop_number from Route_stops rs "
					+ "where rs.route = tt.trip.route "
					+ "and rs.stop = :sout)").setTimestamp("tout", out).setParameter("sout", arrival);
			}
			Query price_query = null, seats_query = null, count_query = null;
			if (minprice != null || maxprice != null) {
				price_query = session.createQuery("select tt.trip from Time_table tt "
					+ "where tt.stop_number > (select rs.stop_number from Route_stops rs "
					+ "where rs.route = tt.trip.route and rs.stop = :sin)"
					+ "and tt.stop_number <= (select rs.stop_number from Route_stops rs "
					+ "where rs.route = tt.trip.route and rs.stop = :sout)"
					+ "group by tt.trip "
					+ "having sum(tt.price) >= :pmin "
					+ "and sum(tt.price) <= :pmax ").setInteger("pmin", minprice).setInteger("pmax", maxprice)
					.setParameter("sin", departure).setParameter("sout", arrival);
			}
			if (seats != null) {
				seats_query = session.createQuery("select tt.trip from Time_table tt "
					+ "where tt.stop_number >= (select rs.stop_number from Route_stops rs "
					+ "where rs.route = tt.trip.route and rs.stop = :sin) "
					+ "and tt.stop_number < (select rs.stop_number from Route_stops rs "
					+ "where rs.route = tt.trip.route and rs.stop = :sout) "
					+ "group by tt.trip "
					+ "having min(tt.vacant_seats) >= :seats")
					.setParameter("sin", departure).setParameter("sout", arrival).setInteger("seats", seats);
			}
			if (minstops != null || maxstops != null) {
				count_query = session.createQuery("select t from Trip t "
						+ "where (select rs.stop_number from Route_stops rs "
						+ "where rs.route = t.route and rs.stop = :sout) - "
						+ "(select rs.stop_number from Route_stops rs "
						+ "where rs.route = t.route and rs.stop = :sin) "
						+ "between :mins and :maxs")
						.setParameter("sin", departure).setParameter("sout", arrival)
						.setInteger("mins", minstops).setInteger("maxs", maxstops);
			}
			String str_query = "select t from Trip t where t.route in (:rid) ";
			if (minprice != null || maxprice != null) {
				if (!price_query.list().isEmpty())
					str_query += "and t in (:pid) ";
				else
					return result;
			}
			if (seats != null) {
				if (!seats_query.list().isEmpty())
					str_query += "and t in (:sid) ";
				else
					return result;
			}
			if (minstops != null || maxstops != null) {
				if (!count_query.list().isEmpty())
					str_query += "and t in (:stid) ";
				else
					return result;
			}
			if (companies != null && !companies.isEmpty())
				str_query += "and t.company in (:cid) ";
			if (in != null)
				if (!in_query.list().isEmpty())
					str_query += "and t in (:iid) ";
				else
					return result;
			if (out != null)
				if (!out_query.list().isEmpty())
					str_query += "and t in (:oid) ";
				else
					return result;
			if (number != null)
				str_query += "and t.route.number = :rn ";
			Query query = session.createQuery(str_query);
			query.setParameterList("rid", stops_query.list());
			if (minprice != null || maxprice != null) 
				query.setParameterList("pid", price_query.list());
			if (seats != null)
				query.setParameterList("sid", seats_query.list());
			if (minstops != null || maxstops != null)
				query.setParameterList("stid", count_query.list());
			if (number != null)
				query.setInteger("rn", number);
			if (companies != null && !companies.isEmpty())
				query.setParameterList("cid", companies);
			if (in != null)
				query.setParameterList("iid", in_query.list());
			if (out != null)
				query.setParameterList("oid", out_query.list());
			result = query.list();
			session.getTransaction().commit();
			return result;
		} finally {
			if (session != null && session.isOpen())
				session.close();
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Trip> getActive() {
		Session session = null;
		List<Trip> result = new ArrayList<Trip>();
		try {
			session = HibernateUtil.getFactory().openSession();
			session.beginTransaction();
			Query query = session.createQuery("select distinct tt.trip from Time_table tt "
				+ "where tt.departure >= :tin and tt.stop_number = "
				+ "(select rs.stop_number from Route_stops rs "
				+ "where rs.route = tt.trip.route "
				+ "and rs.stop_number = 1) and tt.trip.route.active = true "
				+ "order by tt.departure asc ").setTimestamp("tin", new Date());
			result = query.list();
			session.getTransaction().commit();
			return result;
		} finally {
			if (session != null && session.isOpen())
				session.close();
		}
	}
}
